package com.ssyt.hiringtwo.common;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import com.ssyt.hiringtwo.entity.PageModel;


/**
 * 简化数据库操作的工具类
 * 
 * @author Administrator
 */
public class DBUtil {

    /**
     * 得到数据库连接
     * 
     * @return
     */
    public static Connection getConnection() {
        Connection connection = null;
        try {
            // 1、加载驱动
            Class.forName(Constant.DRIVER);
            // 2、连接数据库
            connection = DriverManager.getConnection(Constant.URL, Constant.USER, Constant.PASSWORD);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("驱动加载失败！");
        } catch (SQLException e) {
            throw new RuntimeException("数据库连接失败");
        }
        return connection;
    }

    /**
     * 关闭ResultSet
     * 
     * @param preparedStatement
     */
    public static void close(ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException("数据库资源释放失败");
        }
    }

    /**
     * 关闭PreparedStatement
     * 
     * @param preparedStatement
     */
    public static void close(PreparedStatement preparedStatement) {
        try {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException("数据库资源释放失败");
        }
    }

    /**
     * 关闭Connection
     * 
     * @param preparedStatement
     */
    public static void close(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException("数据库资源释放失败");
        }
    }

    /**
     * 增、删、改的数据库资源释放
     * 
     * @param connection
     * @param preparedStatement
     */
    public static void close(Connection connection, PreparedStatement preparedStatement) {
        close(preparedStatement);
        close(connection);
    }

    /**
     * 增、删、改、查的数据库资源释放
     * 
     * @param connection
     * @param preparedStatement
     */
    public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
        close(resultSet);
        close(preparedStatement);
        close(connection);
    }

    /**
     * 数据更新操作<br>
     * 注：此方法仅用于单个更新操作，如果有事务的情况还得手动控制Connection并开启事务操作！
     * 
     * @param sql
     *            传入预编译的sql语句
     * @param paramList
     *            向模版中?赋值的参数
     * @return 受影响的行数
     * @throws RuntimeException
     *             如果更新失败会返回此异常
     */
    public static int executeUpdate(String sql, List<Object> paramList) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            setParameter(paramList, preparedStatement);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("数据更新失败");
        } finally {
            DBUtil.close(connection, preparedStatement);
        }
    }

    /**
     * 数据更新操作<br>
     * 注：此方法仅用于单个更新操作，如果有事务的情况还得手动控制Connection并开启事务操作！
     * 
     * @param sql
     *            传入预编译的sql语句
     * @param params
     *            向模版中?赋值的参数
     * @return 受影响的行数
     * @throws RuntimeException
     *             如果更新失败会返回此异常
     */
    public static int executeUpdate(String sql, Object... params) {
        return executeUpdate(sql, Arrays.asList(params));
    }

    /**
     * 查询：返回对象集合
     * 
     * @param clazz
     * @param sql
     * @param paramList
     * @return
     */
    public static <T> List<T> executeQuery(Class<T> clazz, String sql, List<Object> paramList) {
        List<T> resultList = new ArrayList<T>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            setParameter(paramList, preparedStatement);
            resultSet = preparedStatement.executeQuery();
            // 读取数据库表结构
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            // 得到有多少列
            int columns = resultSetMetaData.getColumnCount();
            // 循环每条结果，并把结果封装成具体的实体对象，最后放到返回集合中
            while (resultSet.next()) {
                T obj = clazz.newInstance();
                // 对每列循环，把对应的值赋对obj的相应属性
                for (int i = 1; i <= columns; i++) {
                    // getColumnLabel会去查询数据的别名，如果没有别名就取原名字
                    String columnLabel = resultSetMetaData.getColumnLabel(i);
                    // 循环所有的属性
                    Field[] fields = clazz.getDeclaredFields();
                    for (Field field : fields) {
                        field.setAccessible(true);
                        Class<?> fieldClass = field.getType();
                        String fieldName = field.getName();
                        // 某个属性有大写，表示需要属性名与数据库列名对应
                        if (isContainsAlphaUpper(fieldName)) {
                            String columnName = toColumnName(fieldName);
                            if (columnName.equals(columnLabel)) {
                                setFieldValue(resultSet, obj, columnLabel, field, fieldClass);
                                break;
                            }
                        } else {
                            // 表示属性名与数据库列名相同
                            if (fieldName.equals(columnLabel)) {
                                setFieldValue(resultSet, obj, columnLabel, field, fieldClass);
                                break;
                            }
                        }
                    }
                }
                resultList.add(obj);
            }
            return resultList;
        } catch (SQLException | InstantiationException | IllegalAccessException | IllegalArgumentException | SecurityException e) {
            throw new RuntimeException("数据查询失败");
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
    }

    /**
     * 判断字符串里是否有大写字母
     * 
     * @param fieldName
     * @return
     */
    private static boolean isContainsAlphaUpper(String fieldName) {
        for (int i = 0; i < fieldName.length(); i++) {
            int ascii = fieldName.charAt(i);
            if (ascii >= 65 && ascii <= 90) {
                return true;
            }
        }
        return false;
    }

    /**
     * Java属性 -> 数据库字段名：helloWorld -> hello_world
     * 
     * @param fieldName
     * @return
     */
    private static String toColumnName(String fieldName) {
        char[] chars = fieldName.toCharArray();
        StringBuilder sb = new StringBuilder();
        for (char c : chars) {
            int ascii = c;
            if (ascii >= 65 && ascii <= 90) {
                sb.append("_" + (char) (c + 32));
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }

    /**
     * 利用反射实现JavaBean的自动赋值
     * 
     * @param resultSet
     * @param obj
     * @param columnName
     * @param field
     * @param fieldClass
     * @throws IllegalAccessException
     * @throws SQLException
     */
    private static <T> void setFieldValue(ResultSet resultSet, T obj, String columnName, Field field, Class<?> fieldClass) throws IllegalAccessException, SQLException {
        // FIXME 根据业务扩展
        if (fieldClass.equals(int.class) || fieldClass.equals(Integer.class)) {
            field.set(obj, resultSet.getInt(columnName));
        } else if (fieldClass.equals(String.class)) {
            field.set(obj, resultSet.getString(columnName));
        } else if (fieldClass.equals(BigDecimal.class)) {
            field.set(obj, resultSet.getBigDecimal(columnName));
        } else if (fieldClass.equals(Date.class)) {
            field.set(obj, resultSet.getTimestamp(columnName));
        }
    }

    /**
     * 查询：返回对象集合
     * 
     * @param clazz
     * @param sql
     * @param paramList
     * @return
     */
    public static <T> List<T> executeQuery(Class<T> clazz, String sql, Object... params) {
        return executeQuery(clazz, sql, Arrays.asList(params));
    }

    /**
     * 查询：返回单一对象
     * 
     * @param clazz
     * @param sql
     * @param paramList
     * @return
     */
    public static <T> T getUniqueResult(Class<T> clazz, String sql, List<Object> paramList) {
        List<T> list = executeQuery(clazz, sql, paramList);
        if (list.isEmpty()) {
            return null;
        } else {
            return list.get(0);
        }
    }

    /**
     * 查询：返回单一对象
     * 
     * @param clazz
     * @param sql
     * @param paramList
     * @return
     */
    public static <T> T getUniqueResult(Class<T> clazz, String sql, Object... params) {
        return getUniqueResult(clazz, sql, Arrays.asList(params));
    }

    /**
     * 查询：返回分页对象
     * 
     * @param clazz
     * @param sql
     * @param paramList
     * @return
     */
    public static <T> PageModel<T> executeQueryByPage(Class<T> clazz, int pageSize, int pageNo, String sql, List<Object> paramList) {
        // 数据集合
        List<T> list = executeQuery(clazz, sql, paramList);

        // 分页总数
        sql = sql.toUpperCase();
        String countSql = "SELECT COUNT(*) " + sql.substring(sql.indexOf("FROM"), sql.indexOf("LIMIT"));
        int count = getCountByPage(countSql, paramList);

        return new PageModel<T>(list, count, pageSize, pageNo);
    }

    /**
     * 获取分页总数
     * 
     * @param countSql
     * @param paramList
     * @return
     */
    private static int getCountByPage(String countSql, List<Object> paramList) {
        paramList = paramList.subList(0, paramList.size() - 2);
        return getCount(countSql, paramList);
    }

    /**
     * 获取总数
     * 
     * @param countSql
     * @param paramList
     * @return
     */
    public static int getCount(String countSql, List<Object> paramList) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(countSql);
            setParameter(paramList, preparedStatement);
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            return resultSet.getInt(1);
        } catch (Exception e) {
            throw new RuntimeException("获取总数失败");
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
    }

    public static int getCount(String countSql, Object... params) {
        return getCount(countSql, Arrays.asList(params));
    }

    /**
     * 查询：返回分页对象
     * 
     * @param clazz
     * @param sql
     * @param paramList
     * @return
     */
    public static <T> PageModel<T> executeQueryByPage(Class<T> clazz, int pageSize, int pageNo, String sql, Object... params) {
        return executeQueryByPage(clazz, pageSize, pageNo, sql, Arrays.asList(params));
    }

    /**
     * 参数设置
     * 
     * @param paramList
     * @param preparedStatement
     * @throws SQLException
     */
    public static void setParameter(List<Object> paramList, PreparedStatement preparedStatement) throws SQLException {
        if (paramList != null) {
            for (int i = 0; i < paramList.size(); i++) {
                preparedStatement.setObject(i + 1, paramList.get(i));
            }
        }
    }
}
